﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataClassLayer
{
   public class PlayerTransferCollection
    {
        public PlayerTransferCollection()
        {
            
        }
        public bool Add(PlayerTransfer playertransfer)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Insert into SOCCER_PLAYER_TRANSFERS (FromClubID,ToClubID,TransferDate,PlayerID) Values
                   (" + playertransfer.FromClubID + ","
                   + playertransfer.ToClubID + ",Convert(Datetime,'"
                   + playertransfer.TransferDate +"',103)," 
                   + playertransfer.PlayerID + ")";
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Edit(PlayerTransfer playertransfer)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Update SOCCER_PLAYER_TRANSFERS Set 
                     FromClubID=" + playertransfer.FromClubID +
                     ",ToClubID=" + playertransfer.ToClubID +
                     ",TransferDate=Convert(Datetime,'" + playertransfer.TransferDate +
                     "',103),PlayerID=" + playertransfer.PlayerID +
                     " Where ID=" + playertransfer.ID;
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            } 
        }
        public bool Delete(PlayerTransfer playertransfer)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Delete From SOCCER_PLAYER_TRANSFERS Where ID= " + playertransfer.ID; 
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public DataTable FillList(PlayerTransfer playertransfer)
        {
            try
            {
                string wherestatement = WhereStatement(playertransfer);
                string command = @"SELECT        SOCCER_PLAYER_TRANSFERS.ID, SOCCER_PLAYER_TRANSFERS.FromClubID, SOCCER_CLUB_INFO.Name AS FromClubName, 
                         SOCCER_PLAYER_TRANSFERS.ToClubID, SOCCER_CLUB_INFO_1.Name AS ToClubName, SOCCER_PLAYER_TRANSFERS.TransferDate, 
                         SOCCER_PLAYER_TRANSFERS.PlayerID, SOCCER_SINGLE_PLAYER_INFO.Name AS PlayerName
                         FROM            SOCCER_PLAYER_TRANSFERS INNER JOIN
                         SOCCER_CLUB_INFO ON SOCCER_PLAYER_TRANSFERS.ID = SOCCER_CLUB_INFO.ID INNER JOIN
                         SOCCER_CLUB_INFO AS SOCCER_CLUB_INFO_1 ON SOCCER_PLAYER_TRANSFERS.ToClubID = SOCCER_CLUB_INFO_1.ID INNER JOIN
                         SOCCER_SINGLE_PLAYER_INFO ON SOCCER_PLAYER_TRANSFERS.PlayerID = SOCCER_SINGLE_PLAYER_INFO.ID";
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    command += " Where " + wherestatement;
                }
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                return table;
            }
            catch
            {
                return null;
            }
        }
        public int MaxID()
        {
            try
            {
                int Maxid = 1;
                string command = @"SELECT    Max(ID) AS MaxID FROM   SOCCER_PLAYER_TRANSFERS";
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                if (table.Rows.Count == 1)
                {
                    Maxid = int.Parse(table.Rows[0]["MaxID"].ToString());
                }
                return Maxid;
            }
            catch
            {
                return 1;
            }
        }
        public string WhereStatement(PlayerTransfer playertransfer)
        {
            string wherestatement = "";
            if (playertransfer.ID > 0)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And ID= " + playertransfer.ID;
                }
                else
                {
                    wherestatement += "ID= " + playertransfer.ID;
                }
            }
            if (playertransfer.PlayerID > 0)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And PlayerID= " + playertransfer.PlayerID;
                }
                else
                {
                    wherestatement += "PlayerID= " + playertransfer.PlayerID;
                }
            }
            return wherestatement;
        }
    }
}
